File manager

File manager - Edit - /home/autoph/public_html/projects/tickets-autohub/app/Http/Controllers/AuthenticationController.php

Back
<?php namespace App\Http\Controllers; use App\Models\Configuration; use App\Models\Department; use App\Models\Report; use App\Models\User; use App\Models\Vehicle; use Carbon\Carbon; // use Illuminate\Contracts\Validation\Validator; use Illuminate\Http\Request; use Illuminate\Support\Facades\Auth; use Illuminate\Support\Facades\Broadcast; use Illuminate\Support\Facades\DB; use Illuminate\Support\Facades\Validator; use League\Flysystem\Config; use Pusher\Pusher; use Silber\Bouncer\Bouncer; use Silber\Bouncer\Database\Queries\Roles; use Silber\Bouncer\Database\Role; class AuthenticationController extends Controller { private $vehicle, $report; public function __construct(){ $this->report = new Report; $this->vehicle = new Vehicle; } public function authenticateChannel(Request $request){ // dd($request->all()); // $socketId = $request->input('socket_id'); // $channelName = $request->input('channel_name'); // $pusher = new Pusher( // env('PUSHER_APP_KEY'), // env('PUSHER_APP_SECRET'), // env('PUSHER_APP_ID'), // [ // 'cluster' => env('PUSHER_APP_CLUSTER'), // 'useTLS' => true // ] // ); // $auth = $pusher->authorizeChannel($channelName, $socketId); // return response($auth); return Broadcast::auth($request); } public function loginCredentials($username, $password){ $info = User::where('asa_id',"=",$username)->first(); if($info){ if($info && password_verify($password, $info->password)){ Auth::login($info); } } else { return abort(419,"Invalid Credentials"); } } // public function login(Request $request){ // $userLogin = User::where('asa_id',$request->input('username'))->first(); // if($userLogin){ // if(password_verify($request->password, $userLogin->password)){ // Auth::login($userLogin); // $token = auth()->user()->createToken('ticketing')->accessToken; // return response()->json(['message'=>$userLogin,'token'=>$token],200); // } // } // else{ // // $this->asaLogin($request->input('username'),$request->password); // $username = $request->input('username'); // $password = $request->password; // $url = 'https://autohub.ph/connect/api/v1/asa/api.php'; // $parameters = [ // 'key' => "99797807845605376", // 'username' => $username, // 'password' => $password, // ]; // $ch = curl_init(); // curl_setopt($ch, CURLOPT_URL, $url); // curl_setopt($ch, CURLOPT_POST, 1); // curl_setopt($ch, CURLOPT_POSTFIELDS, http_build_query($parameters)); // curl_setopt($ch, CURLOPT_RETURNTRANSFER, true); // $op = curl_exec($ch); // $err = curl_error($ch); // curl_close($ch); // if ($err) { // return response()->json(['message' => 'Error connecting to ASA API: ' . $err], 500); // } // $response = json_decode($op, true); // if (!$response \|\| $response['status'] != 1) { // // Attempt local login if ASA login fails // $user = User::where('asa_id', $username)->first(); // if ($user && password_verify($password, $user->password)) { // // Auth::login($user); // $role = DB::table('assigned_roles')->where('entity_id',$user->id)->first(); // if(!$role) // { // $createRole = [ // 'role_id' => 12, // 'entity_id' => $user->id, // 'entity_id' => 'App\Models\User' // ]; // DB::table('assigned_roles')->insert($createRole); // $this->updateOrCreateUser($response, $password); // } // $token = auth()->user()->createToken('ticketing')->accessToken; // return response()->json(['message' => $user, 'token' => $token], 200); // } // return response()->json(['message' => 'Invalid username or password'], 401); // } // else{ // // dd($response); // $new_user = [ // 'firstname' => $response['u_fname'], // 'lastname' => $response['u_lname'], // 'email' => $response['email'], // 'position' => $response['u_position'], // 'contact' => $response['usr_mobile'], // 'employee_id' => $response['employee_id'], // 'department_id' => $response['dept_id'], // 'department_name' => $response['dept_name'], // 'dealership_id' => $response['dealer'], // Uncomment if needed // 'dealer_code' => $response['deal_code'], // 'company_id' => base64_encode($response['company']), // 'company_code' => $response['comp_code'], // 'company_id' => $response['company'], // 'mobile' => $response['usr_mobile'], // 'password' => base64_encode($password), // ]; // return response()->json(['message' => 'new-user', 'data' => $new_user], 201); // // Extract necessary user data from the response // } // } // } // this function is used for login and fetching the data from the ASA api public function login(Request $request) { $username = $request->input('username'); $password = $request->input('password'); $userLogin = User::where('asa_id', $username)->first(); if ($userLogin && password_verify($request->password, $userLogin->password)) { Auth::login($userLogin); $token = auth()->user()->createToken('ticketing')->accessToken; return response()->json(['message' => $userLogin, 'token' => $token], 200); } return $this->asaLogin($username, $password); } private function asaLogin($username, $password) { try { $url = 'https://autohub.ph/connect/api/v1/asa/api.php'; $parameters = [ 'key' => "99797807845605376", 'username' => $username, 'password' => $password, ]; $ch = curl_init(); curl_setopt($ch, CURLOPT_URL, $url); curl_setopt($ch, CURLOPT_POST, 1); curl_setopt($ch, CURLOPT_POSTFIELDS, http_build_query($parameters)); curl_setopt($ch, CURLOPT_RETURNTRANSFER, true); $op = curl_exec($ch); $err = curl_error($ch); curl_close($ch); if ($err) { return response()->json(['message' => 'Error connecting to ASA API: ' . $err], 500); } $response = json_decode($op, true); if (!$response \|\| $response['status'] != 1) { // Attempt local login if ASA login fails // $user = User::where('asa_id', $username)->first(); // if ($user && password_verify($password, $user->password)) { // $role = DB::table('assigned_roles')->where('entity_id',$user->id)->first(); // if(!$role) // { // $newUser = [ // 'firstname' => $response['u_fname'], // 'lastname' => $response['u_lname'], // 'email' => $response['email'], // 'position' => $response['u_position'], // 'contact' => $response['usr_mobile'], // 'employee_id' => $response['employee_id'], // 'department_id' => $response['dept_id'], // 'department_name' => $response['dept_name'], // 'dealership_id' => $response['dealer'], // 'dealer_code' => $response['deal_code'], // 'company_id' => base64_encode($response['company']), // 'company_code' => $response['comp_code'], // 'mobile' => $response['usr_mobile'], // 'password' => base64_encode($password), // ]; // return response()->json(['message' => 'new-user', 'data' => $newUser], 201); // } // $token = auth()->user()->createToken('ticketing')->accessToken; // return response()->json(['message' => $user, 'token' => $token], 200); // } // return response()->json(['status' => 0, 'message' => $response['message']]); return response()->json(['message' => 'Invalid username or password'], 401); } else{ $user = User::where('asa_id', $username)->first(); if($user){ if (password_verify($password, $user->password)) { $token = auth()->user()->createToken('ticketing')->accessToken; return response()->json(['message' => $user, 'token' => $token], 200); } else { // dd(bcrypt(($password))); $newUser = [ 'firstname' => $response['u_fname'], 'lastname' => $response['u_lname'], 'email' => $response['email'], 'position' => $response['u_position'], 'contact' => $response['usr_mobile'], 'employee_id' => $response['employee_id'], 'department_id' => $response['dept_id'], 'department_name' => $response['dept_name'], 'dealership_id' => $response['dealer'], 'dealer_code' => $response['deal_code'], 'company_id' => base64_encode($response['company']), 'company_code' => $response['comp_code'], 'mobile' => $response['usr_mobile'], 'password' => base64_encode($password), ]; return response()->json(['message' => 'new-user', 'data' => $newUser], 201); } } } $newUser = [ 'firstname' => $response['u_fname'], 'lastname' => $response['u_lname'], 'email' => $response['email'], 'position' => $response['u_position'], 'contact' => $response['usr_mobile'], 'employee_id' => $response['employee_id'], 'department_id' => $response['dept_id'], 'department_name' => $response['dept_name'], 'dealership_id' => $response['dealer'], 'dealer_code' => $response['deal_code'], 'company_id' => base64_encode($response['company']), 'company_code' => $response['comp_code'], 'mobile' => $response['usr_mobile'], 'password' => base64_encode($password), ]; return response()->json(['message' => 'new-user', 'data' => $newUser], 201); } catch (\Exception $e) { return response()->json(['message' => 'An error occurred: ' . $e->getMessage()], 500); } } public function updateInfo(Request $request){ DB::beginTransaction(); // Start transaction try { // Fetch Dealership and Company data $dealership = DB::connection('autohub_connect') ->table('vts_dealerships') ->select('deal_code', 'deal_name') ->where('deal_id', $request->_dealership) ->first(); $companies = DB::connection('autohub_connect') ->table('vts_company') ->select('comp_code', 'comp_name') ->where('comp_id', $request->_company) ->where('comp_status', 1) ->first(); if (!$dealership \|\| !$companies) { return response()->json(['error' => 'Invalid Dealership or Company'], 400); } $existingUser = DB::table('users')->where('asa_id', $request->_empID)->first(); if ($existingUser) { // Update existing user DB::table('users') ->where('asa_id', $request->_empID) ->update([ 'f_name' => $request->_fName, 'l_name' => $request->_lName, 'email' => $request->_email, 'viber' => $request->_viber, 'position' => $request->_position, 'password' => bcrypt(base64_decode($request->password)), 'department_id' => $request->_department, 'deal_code' => $dealership->deal_code, 'comp_code' => $companies->comp_code, 'active' => 1, 'updated_at' => now() ]); $userId = $existingUser->id; } else { $userId = DB::table('users')->insertGetId([ 'asa_id' => $request->_empID, 'f_name' => $request->_fName, 'l_name' => $request->_lName, 'email' => $request->_email, 'viber' => $request->_viber, 'position' => $request->_position, 'password' => bcrypt(base64_decode($request->password)), 'department_id' => $request->_department, 'deal_code' => $dealership->deal_code, 'comp_code' => $companies->comp_code, 'active' => 1, 'created_at' => now(), // Ensure timestamps 'updated_at' => now() ]); // Insert role assignment DB::table('assigned_roles')->insert([ 'role_id' => 12, 'entity_id' => $userId, // Use retrieved user ID 'entity_type' => 'App\Models\User' ]); } // Fetch newly created user for authentication $user = User::find($userId); DB::commit(); // Commit transaction Auth::login($user); $token = auth()->user()->createToken('ticketing')->accessToken; return response()->json(['success' => 1,'message'=>'success','token'=>$token], 200); } catch (\Exception $e) { DB::rollBack(); // Rollback transaction on error return response()->json(['error' => $e->getMessage()], 500); } } // private function asaLogin($username, $password) // { // $url = 'https://autohub.ph/connect/api/v1/asa/api.php'; // $parameters = [ // 'key' => "99797807845605376", // 'username' => $username, // 'password' => $password, // ]; // $ch = curl_init(); // curl_setopt($ch, CURLOPT_URL, $url); // curl_setopt($ch, CURLOPT_POST, 1); // curl_setopt($ch, CURLOPT_POSTFIELDS, http_build_query($parameters)); // curl_setopt($ch, CURLOPT_RETURNTRANSFER, true); // $op = curl_exec($ch); // $err = curl_error($ch); // curl_close($ch); // if ($err) { // return response()->json(['message' => 'Error connecting to ASA API: ' . $err], 500); // } // $response = json_decode($op, true); // // dd($response); // if (!$response \|\| $response['status'] != 1) { // // Attempt local login if ASA login fails // $user = User::where('asa_id', $username)->first(); // if ($user && password_verify($password, $user->password)) { // // Auth::login($user); // $role = DB::table('assigned_roles')->where('entity_id',$user->id)->first(); // if(!$role) // { // $createRole = [ // 'role_id' => 12, // 'entity_id' => $user->id, // 'entity_id' => 'App\Models\User' // ]; // DB::table('assigned_roles')->insert($createRole); // $this->updateOrCreateUser($response, $password); // } // $token = auth()->user()->createToken('ticketing')->accessToken; // return response()->json(['message' => $user, 'token' => $token], 200); // } // return response()->json(['message' => 'Invalid username or password'], 401); // } // else{ // // Extract necessary user data from the response // $firstname = $response['u_fname']; // $lastname = $response['u_lname']; // $email = $response['email']; // $contact = $response['usr_mobile']; // $employee_id = $response['employee_id']; // $department_id = $response['dept_id']; // // $dealership_id = $response['dealer']; // $dealer_code = $response['deal_code']; // $company_code = $response['comp_code']; // $company_id = $response['company']; // $mobile = $response['usr_mobile']; // // Redirect with parameters // return redirect()->route('update-info', [ // 'f_name' => $firstname, // Ensure consistency with the route // 'l_name' => $lastname, // 'email' => $email, // 'asa_id' => $employee_id, // 'department_id' => $department_id, // 'deal_code' => $dealer_code, // 'comp_code' => $company_code, // 'password' => $password, // Optional: bcrypt($password) if encrypting // ]); // } // } private function updateOrCreateUser($response, $password) { try { $user = User::updateOrCreate( ['asa_id' => $response['employee_id']], // Match condition [ 'f_name' => $response['u_fname'], 'l_name' => $response['u_lname'], 'email' => $response['email'], 'viber' => $response['usr_mobile'], 'position' => $response['u_position'], 'password' => bcrypt($password), // Use provided password 'department_id' => $response['dept_id'] > 0 ? $response['dept_id'] : 21, // Default to 21 if null or 0 'deal_code' => $response['deal_code'], 'comp_code' => $response['comp_code'], 'active' => 1 ] ); // Assign default role if user is newly created if ($user->wasRecentlyCreated) { $user->assign('user-access'); } // Log in the user Auth::login($user); // Generate token $token = $user->createToken('ticketing')->accessToken; return response()->json(['message' => $user, 'token' => $token], 200); } catch (\Exception $e) { return response()->json(['message' => 'An error occurred: ' . $e->getMessage()], 500); } } // // HR // public function login(Request $request){ // $user = null; // $url = 'https://staging-ahg-hr-api.autohub.ph/api/login'; // $parameters = ['username' => $request->username,'password'=> $request->password]; // $ch = curl_init(); // curl_setopt($ch, CURLOPT_URL, $url); // curl_setopt($ch, CURLOPT_POST, 1); // curl_setopt($ch, CURLOPT_POSTFIELDS, http_build_query($parameters)); // curl_setopt($ch, CURLOPT_RETURNTRANSFER, true); // $op = curl_exec ($ch); // $err = curl_error($ch); // curl_close ($ch); // $response = json_decode($op,true); // if($err) // { // return response()->json(['message' => 'Failed to login. Error '.$err],500); // } // if(isset($response['message']) && $response['message'] =='Unauthorized'){ // return response()->json(['message' => "Unauthorized"],419); // } // if($response){ // dd($response); // $employee_comp_code = $response['roles']['companies']['code']; // $employee_dealer_code = $response['roles']['dealerships']['code']; // $employee = $response['user']['employees']; // $info = User::where('asa_id',"=",$employee['employee_id'])->first(); // // if($info && password_verify($request->password, $info->password)){ // if($info){ // // dd($info); // $info->update( // [ // 'f_name' => $employee['firstname'], // 'l_name' => $employee['lastname'], // 'email' => $employee['email'], // 'position' => $employee['position'], // 'password' => bcrypt($request->password), // ] // ); // Auth::login($info); // return response()->json(['message'=>"Success"]); // } else { // if(!(password_verify($request->password, $info->password))){ // return response()->json(['message'=>"Invalid Password"],419); // } // abort(419,"Invalid Credentials"); // } // } else { // dd('2'); // // $user = User::where('asa_id',$response['employee_id'])->first(); // } // if($user){ // $user->update( // [ // 'f_name' => $response['u_fname'], // 'l_name' => $response['u_lname'], // 'email' => $response['email'], // 'position' => $response['u_position'], // 'password' => bcrypt($request->password) // ] // ); // } else { // $validationRules = [ // 'employee_id' => 'required', // 'u_fname' => 'required\|string\|max:255', // 'u_lname' => 'required\|string\|max:255', // 'email' => 'required\|email', // 'u_position' => 'required\|string\|max:255', // 'dept_id' => 'required\|integer\|min:1', // 'deal_code' => 'required\|string\|max:255', // 'comp_code' => 'required\|string\|max:255', // ]; // $customMessages = [ // 'employee_id.required' => 'The employee ID field is required.', // 'u_fname.required' => 'The first name field is required.', // 'u_lname.required' => 'The last name field is required.', // 'email.required' => 'The email field is required.', // 'email.email' => 'The email must be a valid email address.', // 'u_position.required' => 'The position field is required.', // 'dept_id.required' => 'The department field is required.', // 'dept_id.min' => 'The department field is required.', // 'deal_code.required' => 'The deal code field is required.', // 'comp_code.required' => 'The company code field is required.', // ]; // $validator = Validator::make($response, $validationRules, $customMessages); // if ($validator->fails()) { // $errors = $validator->errors(); // $errorMessages = []; // foreach ($errors->all() as $message) { // $errorMessages[] = $message; // } // return response()->json(['errors' => $errorMessages], 422); // 422 is an HTTP status code for unprocessable entity // } // // $role = Roles::where('name','user')->first(); // $user = new User; // $user->asa_id = $response['employee_id']; // $user->f_name = $response['u_fname']; // $user->l_name = $response['u_lname']; // $user->email = $response['email']; // $user->position = $response['u_position']; // $user->password = bcrypt($request->password); // $user->department_id = ($response['dept_id']); // $user->deal_code = $response['deal_code']; // $user->comp_code = $response['comp_code']; // $user->save(); // $user->assign('user-access'); // } // Auth::login($user); // $token = auth()->user()->createToken('ticketing')->accessToken; // return response()->json(['message'=>$user,'token'=>$token],200); // } // this function is used for public function index(){ if($this->checkMaintenance() === "true"){ abort(503,'SITE UNDER MAINTENANCE'); } // echo'index';exit; $user = User::find(Auth::user()->id); $department = Department::find(Auth::user()->department_id); $dept_id = $user->department_id; $supports = Role::where('name','report-access-role')->first(); $supportCount = $supports->users()->where('department_id',$dept_id)->count(); $supportFrequency = $this->report->getSupportCountFrequency($dept_id); return view('layouts.authentication.guest.index') ->with(['title_page'=>"Home", 'department'=>$department, 'supportCount'=>$supportCount, 'supportFrequency'=>$supportFrequency ]); } // function for rendering the dashboard public function adminDashboard(Request $request){ $data = $request; $date_range = []; if(!empty($data['from_date']) && !empty($data['to_date'])){ switch ($data['from_date']) { case '7': $from = Carbon::now()->subDays(7); $to = Carbon::now(); $date_range = [ $from->toDateString(), $to->toDateString() ]; break; case '14': $from = Carbon::now()->subDays(14); $to = Carbon::now(); $date_range = [ $from->toDateString(), $to->toDateString() ]; break; case '30': $from = Carbon::now()->subDays(30); $to = Carbon::now(); $date_range = [ $from->toDateString(), $to->toDateString() ]; break; default: $from = Carbon::parse($data['from_date']); $to = Carbon::parse($data['to_date']); $date_range = [ $from->toDateString(), $to->toDateString() ]; break; } } $user = User::find(Auth::user()->id); $dept_id = $user->department_id; $ticketCounts = $this->report->ticket_counts($dept_id,$date_range); // $carplusTicketTotal = $this->vehicle::whereBetween('created_at',$date_range)->count(); // $carplusTicketOpen = DB::table('carplus_tickets')->whereBetween('created_at',$date_range)->where('status','open')->count(); // $carplusTicketClosed = DB::table('carplus_tickets')->whereBetween('created_at',$date_range)->where('status','closed')->count(); return response()->json([ 'ticketCounts'=>$ticketCounts[0], 'totalOpenTicketCount'=>$ticketCounts[1], 'totalTransferredTicketCount'=>$ticketCounts[2], 'totalAssignedTicketCount'=>$ticketCounts[3], 'totalPickedTicketCount'=>$ticketCounts[4], 'totalClosedTicketCount'=>$ticketCounts[5], 'carplusTicketTotal'=>$ticketCounts[6], 'carplusTicketOpen'=>$ticketCounts[7], 'carplusTicketClosed'=>$ticketCounts[8], ]); } public function dashboardByDealer(Request $request) { $data = $request; $date_range = []; if(!empty($data['from_date']) && !empty($data['to_date'])){ switch ($data['from_date']) { case '7': $from = Carbon::now()->subDays(7); $to = Carbon::now(); $date_range = [ $from->toDateString(), $to->toDateString() ]; break; case '14': $from = Carbon::now()->subDays(14); $to = Carbon::now(); $date_range = [ $from->toDateString(), $to->toDateString() ]; break; case '30': $from = Carbon::now()->subDays(30); $to = Carbon::now(); $date_range = [ $from->toDateString(), $to->toDateString() ]; break; default: $from = Carbon::parse($data['from_date']); $to = Carbon::parse($data['to_date']); $date_range = [ $from->toDateString(), $to->toDateString() ]; break; } } // CHART SENDER SUMMARY BY COMPANY $userCountsTicket = DB::table('ticket as tckt') ->join('users as u', 'tckt.ticket_creator', '=', 'u.id') ->join('ticket_user as tu', 'tckt.id', '=', 'tu.tix_id') ->select('u.comp_code', DB::raw('COUNT(u.id) as user_count')) ->where('tu.assigned_dept',Auth::user()->department_id) ->whereBetween('tckt.created_at', $date_range) ->groupBy('u.comp_code') ->get(); //Support Ticket $ticketChartCounts = DB::table('ticket as tckt') ->join('users as u', 'tckt.ticket_creator', '=', 'u.id') ->join('ticket_user as tu', 'tckt.id',"=",'tu.tix_id') ->select( 'u.comp_code', DB::raw('SUM(CASE WHEN tckt.status IN ("open", "pending", "ongoing") THEN 1 ELSE 0 END) as open_count'), DB::raw('SUM(CASE WHEN tckt.status = "closed" THEN 1 ELSE 0 END) as closed_count') ) ->where('tu.assigned_dept',Auth::user()->department_id) ->whereBetween('tckt.created_at', $date_range) ->groupBy('u.comp_code') ->get(); // TOP SUPPORT PERSONNEL $supportFrequency = DB::table('ticket_user')->join('users','ticket_user.assigned_to','=','users.id') ->select( DB::raw("CONCAT(users.f_name, ' ', REPLACE(users.l_name, 'Ã±', 'ñ')) as support, COUNT(ticket_user.id) as occurrence_count") ) ->where('ticket_user.assigned_dept',Auth::user()->department_id) ->whereBetween('ticket_user.end_date', $date_range) ->groupBy('ticket_user.assigned_to') ->orderByDesc('occurrence_count') ->limit(5) ->get(); DB::enableQueryLog(); $slaCountPercentage = DB::table('ticket_user') ->select([ 'sla.content', DB::raw('CONCAT(ticket.time_elapsed, " MINS.") as time_elapsed'), 'ticket.ticket_no', 'categories.title as category', DB::raw('CONCAT(sla.content, " MINS.") as content'), DB::raw('CASE WHEN ticket.time_elapsed > sla.content THEN "Over SLA" WHEN ticket.time_elapsed = sla.content THEN "Within SLA" ELSE "Beyond SLA" END AS sla_status'), 'ticket.created_at', 'ticket.start_date', 'ticket.end_date', 'ticket.level', ]) ->join('ticket', 'ticket_user.tix_id', '=', 'ticket.id') ->join('categories', 'ticket.subject', '=', 'categories.id') ->join('service_level_agreement as sla', 'categories.sla_id', '=', 'sla.id') ->where('ticket_user.assigned_dept', Auth::user()->department_id) ->whereBetween('ticket.created_at', $date_range) ->get(); $query = DB::getQueryLog(); // echo'<pre>';print_r($query);echo'</pre>';return; $countBeyondSLA = 0; $countWithinSLA = 0; $countOverSLA = 0; $totalTickets = count($slaCountPercentage); // Total number of tickets // Loop through the query results to calculate counts foreach ($slaCountPercentage as $row) { switch ($row->sla_status) { case 'Beyond SLA': $countBeyondSLA++; break; case 'Within SLA': $countWithinSLA++; break; case 'Over SLA': $countOverSLA++; break; // Handle any other sla_status values if needed } }unset($row); // Calculate percentages $percentageBeyondSLA = ($countBeyondSLA / $totalTickets) * 100; $percentageWithinSLA = ($countWithinSLA / $totalTickets) * 100; $percentageOverSLA = ($countOverSLA / $totalTickets) * 100; return response()->json([ 'userCountsTicket'=>$userCountsTicket, 'ticketChartCounts'=>$ticketChartCounts, 'supportFrequency' => $supportFrequency, 'percentageBeyondSLA'=>$percentageBeyondSLA, 'percentageWithinSLA'=>$percentageWithinSLA, 'percentageOverSLA'=>$percentageOverSLA, ]); } //SUMMARY OF CREATED TICKET BY MONTH public function summaryTicketByYear(Request $request){ $year = $request['year']; // DB::enableQueryLog(); $ticketsCountsByMonth = DB::table('ticket as tckt') ->join('users as u', 'tckt.ticket_creator', '=', 'u.id') ->join('ticket_user as tu', 'tckt.id',"=",'tu.tix_id') ->select( DB::raw('YEAR(tckt.created_at) as year'), DB::raw('MONTH(tckt.created_at) as month'), DB::raw('COUNT(tckt.id) as ticket_count') ) ->where('tu.assigned_dept',Auth::user()->department_id) ->whereYear('tckt.created_at', $year) // Filter by the current year ->groupBy(DB::raw('YEAR(tckt.created_at)'), DB::raw('MONTH(tckt.created_at)')) ->get(); //Support Ticket // $query = DB::getQueryLog(); return response()->json([ 'ticketsCountsByMonth'=>$ticketsCountsByMonth, ]); } public function adminIndex(){ return view('')->with(['title_page' => "Home"]); } public function chartFilterData(Request $request){ $userCounts = DB::table('carplus_tickets as t') ->join('users as u', 't.ticket_creator_id', '=', 'u.id') ->select('u.comp_code', DB::raw('COUNT(u.id) as user_count')) ->groupBy('u.comp_code') ->get(); //Carplus Ticket $userCountsTicket = DB::table('ticket as tckt') ->join('users as u', 'tckt.ticket_creator', '=', 'u.id') ->join('ticket_user as tu', 'tckt.id', '=', 'tu.tix_id') ->select('u.comp_code', DB::raw('COUNT(u.id) as user_count')) ->where('tu.assigned_dept',Auth::user()->department_id) ->groupBy('u.comp_code') ->get(); //Support Ticket $currentYear = date('Y'); // Get the current year $ticketCountsByMonth = DB::table('carplus_tickets as t') ->join('users as u', 't.ticket_creator_id', '=', 'u.id') ->select( DB::raw('YEAR(t.created_at) as year'), DB::raw('MONTH(t.created_at) as month'), DB::raw('COUNT(t.id) as ticket_count') ) ->whereYear('t.created_at', $currentYear) // Filter by the current year ->groupBy(DB::raw('YEAR(t.created_at)'), DB::raw('MONTH(t.created_at)')) ->get(); //Carplus Ticket $ticketsCountsByMonth = DB::table('ticket as tckt') ->join('users as u', 'tckt.ticket_creator', '=', 'u.id') // ->join('ticket_user as tu','tckt.id','=','tu.tix_id') ->join('ticket_user as tu', 'tckt.id',"=",'tu.tix_id') ->select( DB::raw('YEAR(tckt.created_at) as year'), DB::raw('MONTH(tckt.created_at) as month'), DB::raw('COUNT(tckt.id) as ticket_count') ) ->where('tu.assigned_dept',Auth::user()->department_id) // ->where('tu.assigned_to',Auth::user()->department_id) ->whereYear('tckt.created_at', $currentYear) // Filter by the current year ->groupBy(DB::raw('YEAR(tckt.created_at)'), DB::raw('MONTH(tckt.created_at)')) ->get(); //Support Ticket $ticketCounts = DB::table('carplus_tickets as t') ->join('users as u', 't.ticket_creator_id', '=', 'u.id') ->select( 'u.comp_code', DB::raw('SUM(CASE WHEN t.status = "Open" THEN 1 ELSE 0 END) as open_count'), DB::raw('SUM(CASE WHEN t.status = "Closed" THEN 1 ELSE 0 END) as closed_count') ) ->groupBy('u.comp_code') ->get(); //Carplus Ticket $ticketChartCounts = DB::table('ticket as tckt') ->join('users as u', 'tckt.ticket_creator', '=', 'u.id') ->join('ticket_user as tu', 'tckt.id',"=",'tu.tix_id') ->select( 'u.comp_code', DB::raw('SUM(CASE WHEN tckt.status IN ("open", "pending", "ongoing") THEN 1 ELSE 0 END) as open_count'), DB::raw('SUM(CASE WHEN tckt.status = "closed" THEN 1 ELSE 0 END) as closed_count') ) ->where('tu.assigned_dept',Auth::user()->department_id) ->groupBy('u.comp_code') ->get(); //Support Ticket $slaCountPercentage = DB::table('ticket_user') ->select([ // DB::raw('CONCAT(users.f_name, " ", users.l_name) as support'), 'sla.content', DB::raw('CONCAT(ticket.time_elapsed, " MINS.") as time_elapsed'), 'ticket.ticket_no', 'categories.title as category', DB::raw('CONCAT(sla.content, " MINS.") as content'), DB::raw('CASE WHEN ticket.time_elapsed > sla.content THEN "Over SLA" WHEN ticket.time_elapsed = sla.content THEN "Within SLA" ELSE "Beyond SLA" END AS sla_status'), 'ticket.created_at', 'ticket.start_date', 'ticket.end_date', 'ticket.level', ]) // ->leftJoin('users', 'ticket_user.assigned_to', '=', 'users.id') ->join('ticket', 'ticket_user.tix_id', '=', 'ticket.id') ->join('categories', 'ticket.subject', '=', 'categories.id') ->join('service_level_agreement as sla', 'categories.sla_id', '=', 'sla.id') ->where('ticket_user.assigned_dept', Auth::user()->department_id) ->get(); DB::enableQueryLog(); // / TOP SUPPORT PERSONNEL $supportFrequency = DB::table('ticket_user')->join('users','ticket_user.assigned_to','=','users.id') ->select( DB::raw("CONCAT(users.f_name, ' ', REPLACE(users.l_name, 'Ã±', 'ñ')) as support, COUNT(ticket_user.id) as occurrence_count") ) ->where('active',1) ->where('ticket_user.assigned_dept',Auth::user()->department_id) // ->whereBetween('ticket_user.end_date', $date_range) ->groupBy('ticket_user.assigned_to') ->orderByDesc('occurrence_count') ->limit(5) ->get(); // $query = DB::getQueryLog(); // $query = end($query); // dd($query); $countBeyondSLA = 0; $countWithinSLA = 0; $countOverSLA = 0; $totalTickets = count($slaCountPercentage); // Total number of tickets // Loop through the query results to calculate counts foreach ($slaCountPercentage as $row) { switch ($row->sla_status) { case 'Beyond SLA': $countBeyondSLA++; break; case 'Within SLA': $countWithinSLA++; break; case 'Over SLA': $countOverSLA++; break; // Handle any other sla_status values if needed } } // Calculate percentages $percentageBeyondSLA = ($countBeyondSLA / $totalTickets) * 100; $percentageWithinSLA = ($countWithinSLA / $totalTickets) * 100; $percentageOverSLA = ($countOverSLA / $totalTickets) * 100; return response()->json([ 'requestorPerCompany'=>$userCounts, 'ticketCountsByMonth'=>$ticketCountsByMonth, 'ticketCounts'=>$ticketCounts, 'userCountsTicket'=>$userCountsTicket, 'ticketsCountsByMonth'=>$ticketsCountsByMonth, 'ticketChartCounts'=>$ticketChartCounts, 'percentageBeyondSLA'=>$percentageBeyondSLA, 'percentageWithinSLA'=>$percentageWithinSLA, 'percentageOverSLA'=>$percentageOverSLA, 'supportFrequency' => $supportFrequency, ]); } public function chartFilterDataDealer(Request $request){ return response()->json($request); $userCounts = DB::table('carplus_tickets as t') ->join('users as u', 't.ticket_creator_id', '=', 'u.id') ->select('u.comp_code', DB::raw('COUNT(u.id) as user_count')) ->groupBy('u.comp_code') ->get(); //Carplus Ticket $userCountsTicket = DB::table('ticket as tckt') ->join('users as u', 'tckt.ticket_creator', '=', 'u.id') ->join('ticket_user as tu', 'tckt.id', '=', 'tu.tix_id') ->select('u.comp_code', DB::raw('COUNT(u.id) as user_count')) ->where('tu.assigned_dept',Auth::user()->department_id) ->groupBy('u.comp_code') ->get(); //Support Ticket $currentYear = date('Y'); // Get the current year $ticketCountsByMonth = DB::table('carplus_tickets as t') ->join('users as u', 't.ticket_creator_id', '=', 'u.id') ->select( DB::raw('YEAR(t.created_at) as year'), DB::raw('MONTH(t.created_at) as month'), DB::raw('COUNT(t.id) as ticket_count') ) ->whereYear('t.created_at', $currentYear) // Filter by the current year ->groupBy(DB::raw('YEAR(t.created_at)'), DB::raw('MONTH(t.created_at)')) ->get(); //Carplus Ticket $ticketsCountsByMonth = DB::table('ticket as tckt') ->join('users as u', 'tckt.ticket_creator', '=', 'u.id') // ->join('ticket_user as tu','tckt.id','=','tu.tix_id') ->join('ticket_user as tu', 'tckt.id',"=",'tu.tix_id') ->select( DB::raw('YEAR(tckt.created_at) as year'), DB::raw('MONTH(tckt.created_at) as month'), DB::raw('COUNT(tckt.id) as ticket_count') ) ->where('tu.assigned_dept',Auth::user()->department_id) // ->where('tu.assigned_to',Auth::user()->department_id) ->whereYear('tckt.created_at', $currentYear) // Filter by the current year ->groupBy(DB::raw('YEAR(tckt.created_at)'), DB::raw('MONTH(tckt.created_at)')) ->get(); //Support Ticket $ticketCounts = DB::table('carplus_tickets as t') ->join('users as u', 't.ticket_creator_id', '=', 'u.id') ->select( 'u.comp_code', DB::raw('SUM(CASE WHEN t.status = "Open" THEN 1 ELSE 0 END) as open_count'), DB::raw('SUM(CASE WHEN t.status = "Closed" THEN 1 ELSE 0 END) as closed_count') ) ->groupBy('u.comp_code') ->get(); //Carplus Ticket $ticketChartCounts = DB::table('ticket as tckt') ->join('users as u', 'tckt.ticket_creator', '=', 'u.id') ->join('ticket_user as tu', 'tckt.id',"=",'tu.tix_id') ->select( 'u.comp_code', DB::raw('SUM(CASE WHEN tckt.status IN ("open", "pending", "ongoing") THEN 1 ELSE 0 END) as open_count'), DB::raw('SUM(CASE WHEN tckt.status = "closed" THEN 1 ELSE 0 END) as closed_count') ) ->where('tu.assigned_dept',Auth::user()->department_id) ->groupBy('u.comp_code') ->get(); //Support Ticket $slaCountPercentage = DB::table('ticket_user') ->select([ // DB::raw('CONCAT(users.f_name, " ", users.l_name) as support'), 'sla.content', DB::raw('CONCAT(ticket.time_elapsed, " MINS.") as time_elapsed'), 'ticket.ticket_no', 'categories.title as category', DB::raw('CONCAT(sla.content, " MINS.") as content'), DB::raw('CASE WHEN ticket.time_elapsed > sla.content THEN "Over SLA" WHEN ticket.time_elapsed = sla.content THEN "Within SLA" ELSE "Beyond SLA" END AS sla_status'), 'ticket.created_at', 'ticket.start_date', 'ticket.end_date', 'ticket.level', ]) // ->leftJoin('users', 'ticket_user.assigned_to', '=', 'users.id') ->join('ticket', 'ticket_user.tix_id', '=', 'ticket.id') ->join('categories', 'ticket.subject', '=', 'categories.id') ->join('service_level_agreement as sla', 'categories.sla_id', '=', 'sla.id') ->where('ticket_user.assigned_dept', Auth::user()->department_id) ->get(); $countBeyondSLA = 0; $countWithinSLA = 0; $countOverSLA = 0; $totalTickets = count($slaCountPercentage); // Total number of tickets // Loop through the query results to calculate counts foreach ($slaCountPercentage as $row) { switch ($row->sla_status) { case 'Beyond SLA': $countBeyondSLA++; break; case 'Within SLA': $countWithinSLA++; break; case 'Over SLA': $countOverSLA++; break; // Handle any other sla_status values if needed } } // Calculate percentages $percentageBeyondSLA = ($countBeyondSLA / $totalTickets) * 100; $percentageWithinSLA = ($countWithinSLA / $totalTickets) * 100; $percentageOverSLA = ($countOverSLA / $totalTickets) * 100; return response()->json([ 'requestorPerCompany'=>$userCounts, 'ticketCountsByMonth'=>$ticketCountsByMonth, 'ticketCounts'=>$ticketCounts, 'userCountsTicket'=>$userCountsTicket, // SUMMARY SENDER 'ticketsCountsByMonth'=>$ticketsCountsByMonth, 'ticketChartCounts'=>$ticketChartCounts, 'percentageBeyondSLA'=>$percentageBeyondSLA, 'percentageWithinSLA'=>$percentageWithinSLA, 'percentageOverSLA'=>$percentageOverSLA, ]); } public function logout(Request $request){ Auth::guard('web')->logout(); $request->session()->invalidate(); $request->session()->regenerateToken(); return redirect('/login'); } public function systemConfiguration(Request $request){ $maintenance = DB::table('configuration') ->where('name',"=",'maintenance_mode') ->first(); return view('layouts.authentication.admin.system_administration.system_configuration') ->with(['title_page'=>"Site Administration",'maintenance'=>$maintenance->value]); } public function setSystemMaintenance(Request $request){ if(!Auth::user()->can('disable-site')){ return response()->json(['message'=>'Unauthorized request'],403); } $config = DB::table('configuration') ->where('name', 'maintenance_mode') ->update(['value' => $request->data]); if($config){ return response()->json(['message'=>'Success']); } else { return response()->json(['message'=>'Error']); } } public function checkMaintenance(){ $maintenanceMode = DB::table('configuration')->where('name', 'maintenance_mode')->select('value')->first(); return $maintenanceMode->value; } public function TopDepartmentSLAComliance(){ $data = \App\Models\TicketUser::join('departments', function($join) { $join->on('ticket_user.assigned_dept', '=', 'departments.id'); }) ->select( 'departments.dept_name as department_name', DB::raw('COUNT(ticket_user.id) as total_tickets'), DB::raw('SUM(CASE WHEN ticket_user.status = "closed" THEN 1 ELSE 0 END) as closed_tickets'), DB::raw('SUM(CASE WHEN ticket_user.status = "pending" THEN 1 ELSE 0 END) as pending_tickets'), DB::raw('ROUND((SUM(CASE WHEN ticket_user.status = "closed" THEN 1 ELSE 0 END) / COUNT(ticket_user.id)) * 100, 2) as compliance_rate') ) ->groupBy('departments.id', 'departments.dept_name') ->orderBy('total_tickets', 'desc') ->limit(6) ->get(); if($data){ return response()->json(['data'=> $data]); } } public function getTotalTikcetByStatusSummary() { $departmentId = Auth::user()->department_id; // Get the current authenticated department ID $data = DB::table('ticket AS t') ->join('ticket_user','t.id','=','ticket_user.tix_id') ->select( DB::raw("COUNT(t.id) as total_tickets"), DB::raw("SUM(CASE WHEN t.status = 'pending' THEN 1 ELSE 0 END) as open_tickets"), DB::raw("SUM(CASE WHEN ticket_user.ticket_condition = 'transferred' THEN 1 ELSE 0 END) as transferred_tickets"), DB::raw("SUM(CASE WHEN ticket_user.ticket_condition = 'assigned' THEN 1 ELSE 0 END) as assigned_tickets"), DB::raw("SUM(CASE WHEN ticket_user.ticket_condition = 'picked' THEN 1 ELSE 0 END) as picked_tickets"), DB::raw("SUM(CASE WHEN t.status = 'closed' THEN 1 ELSE 0 END) as closed_tickets"), // DB::raw("SUM(CASE WHEN t.`level` = 'urgent' THEN 1 ELSE 0 END) as urgent"), // Alias with backticks // DB::raw("SUM(CASE WHEN t.`level` = 'mid' THEN 1 ELSE 0 END) as medium"), // Alias with backticks // DB::raw("SUM(CASE WHEN t.`level` = 'low' THEN 1 ELSE 0 END) as low") // Alias with backticks ) ->where('ticket_user.assigned_dept', $departmentId) ->first(); return response()->json(['data' => $data]); } public function getTicketPriorities() { $departmentId = Auth::user()->department_id; // Get the current authenticated department ID $data = DB::table('ticket AS t') ->join('ticket_user','t.id','=','ticket_user.tix_id') ->select( DB::raw("SUM(CASE WHEN t.`level` = 'urgent' THEN 1 ELSE 0 END) as urgent"), // Alias with backticks DB::raw("SUM(CASE WHEN t.`level` = 'mid' THEN 1 ELSE 0 END) as medium"), // Alias with backticks DB::raw("SUM(CASE WHEN t.`level` = 'low' THEN 1 ELSE 0 END) as low") // Alias with backticks ) ->where('ticket_user.assigned_dept', $departmentId) ->first(); return response()->json(['data' => $data]); } public function totalMonthlyTicket(Request $request){ $year = $request->input('year', Carbon::now()->year); $departmentId = Auth::user()->department_id; $data = DB::table('ticket') ->join('users', 'ticket.ticket_creator', '=', 'users.id') ->leftJoin('ticket_user', 'ticket.id', '=', 'ticket_user.tix_id') ->leftJoin('departments', 'ticket_user.assigned_dept', '=', 'departments.id') ->select( DB::raw('MONTH(ticket.created_at) as month'), DB::raw('COUNT(ticket.id) as total') ) ->where('ticket_user.assigned_dept', $departmentId) ->whereYear('ticket.created_at', $year) ->groupBy('month') ->orderBy('month') ->get(); return response()->json($data); } }

| ver. 1.4 | . | PHP 8.1.32 | Generation time: 0.05 | proxy | phpinfo | Settings